using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Collections.Generic;

using System.Configuration;

using DAL.util;

namespace DAL.sql2005.common
{
    public class DataAccessManager : IDisposable
    {
        private string f_ConnectionString;
        private SqlConnection f_DBConnection;

        /// <summary>
        /// Gets or sets the Database connection.
        /// </summary>
        /// <value>The DB conn.</value>
        public SqlConnection DBConn
        {
            get 
            {
                if(f_DBConnection == null)
                    f_DBConnection = new SqlConnection(f_ConnectionString);

                return f_DBConnection;
            }
            set
            {
                f_DBConnection = value;
            }
        }

        /// <summary>
        /// Provides management of Data access layer
        /// </summary>
        /// <param name="databaseName">the key in the config filewhich is stores the connection string to use.</param>
        public DataAccessManager(string databaseName)
        {
            f_ConnectionString = (string)ConfigurationManager.ConnectionStrings[databaseName].ToString();
        }


        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <param name="sproc">The sproc.</param>
        /// <param name="list">The list.</param>
        public void ExecuteNonQuery(string sproc, params object[] list)
        {
            SqlCommand DBCommand = new SqlCommand();
            DBCommand.CommandType = CommandType.StoredProcedure;
            DBCommand.CommandText = sproc;
            DBCommand.Connection = DBConn;

            if (DBConn.State == ConnectionState.Closed)
                DBConn.Open();

            SqlCommandBuilder.DeriveParameters(DBCommand);

            if (list == null && DBCommand.Parameters.Count > 0)
                throw new Exception(" Wrong number of parameters sent to method : ExecuteNonQuery");

            int Count;
            // the first 'parameter' in a parameter collection
            // is the RETURN_VALUE = which we ignore :
            for (Count = 1; Count < DBCommand.Parameters.Count; Count++)
            {
                DBCommand.Parameters[Count].Value = list[Count - 1];
            }

            DBCommand.ExecuteNonQuery();
            DBCommand = null;

            DBConn.Close();
            DBConn = null;
        }

        /// <summary>
        /// Gets the data table from stored procedure.
        /// </summary>
        /// <param name="sproc">The sproc.</param>
        /// <param name="list">The list.</param>
        /// <returns></returns>
        public DataTable GetDataTableFromStoredProcedure(string sproc, params object[] list)
        {
            SqlCommand DBCommand = new SqlCommand();
            DBCommand.CommandType = CommandType.StoredProcedure;
            DBCommand.CommandText = sproc;
            DBCommand.Connection = DBConn;

            if (DBConn.State == ConnectionState.Closed)
                DBConn.Open();

            SqlCommandBuilder.DeriveParameters(DBCommand);

            if (list == null && DBCommand.Parameters.Count > 0)
                throw new Exception(" Wrong number of parameters sent to method : ExecuteNonQuery");

            int Count;
            // the first 'parameter' in a parameter collection
            // is the RETURN_VALUE = which we ignore :
            for (Count = 1; Count < DBCommand.Parameters.Count; Count++)
            {
                DBCommand.Parameters[Count].Value = list[Count - 1];
            }

            SqlDataAdapter DA = new SqlDataAdapter(DBCommand);

            DataTable ReturnTable = new DataTable(sproc + "_Results");

            DA.Fill(ReturnTable);

            DA = null;
            DBCommand = null;

            DBConn.Close();
            DBConn = null;

            return ReturnTable;
        }


        /// <summary>
        /// Gets a data table using a SQL text query.
        /// </summary>
        /// <param name="sqlText">The SQL text.</param>
        /// <returns></returns>
        public DataTable GetDataTableFromSQLText(string sqlText)
        {
            SqlCommand DBCommand = new SqlCommand();
            DBCommand.CommandType = CommandType.Text;
            DBCommand.CommandText = sqlText;
            DBCommand.Connection = DBConn;

            if (DBConn.State == ConnectionState.Closed)
                DBConn.Open();

            SqlDataAdapter DA = new SqlDataAdapter(DBCommand);

            DataTable ReturnTable = new DataTable("_Results");

            DA.Fill(ReturnTable);

            DA = null;
            DBCommand = null;

            DBConn.Close();
            DBConn = null;

            return ReturnTable;
        }

        /// <summary>
        /// Gets a scalar value from a SQL text query.
        /// </summary>
        /// <param name="sqlText">The SQL text.</param>
        /// <returns></returns>
        public object GetScalarFromSQLText(string sqlText)
        {
            SqlCommand DBCommand = new SqlCommand();
            DBCommand.CommandType = CommandType.Text;
            DBCommand.CommandText = sqlText;
            DBCommand.Connection = DBConn;

            if (DBConn.State == ConnectionState.Closed)
                DBConn.Open();

            object rtn = DBCommand.ExecuteScalar();

            DBCommand = null;

            DBConn.Close();
            DBConn = null;

            return rtn;
        }


        #region IDisposable Members

        /// <summary>
        /// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
        /// </summary>
        public void Dispose()
        {
            if (f_DBConnection != null)
            {
                if (f_DBConnection.State != ConnectionState.Closed)
                    f_DBConnection.Close();

                f_DBConnection.Dispose();
            }
        }

        #endregion
    }
}
